1 備份概覽

按備份方法分類：

• Hot Backup（熱備）

  在線備份，不影響數據庫運行

• Cold Backup（冷備）

  離線備份，通過拷貝數據庫物理文件

• Warm Backup（溫備）

  在線進行，但需要加讀鎖保證數據一致性

按備份文件內容分類：

• 邏輯備份

  備份后的內容是可讀的，通常形式為SQL語句的文本文件，或是表內的實際數據

  優勢：呈現直觀，適用于升級、遷移等工作，缺點：恢復用時較長

• 裸文件備份

  拷貝數據庫的物理文件，不關心數據庫的運行狀態，通常這類備份恢復的時間較短

按備份數據庫的內容分類：

• 完全備份：對當前數據庫的完整備份

• 增量備份：在完全備份的基礎上，對更新的數據進行備份

• 日志備份：指的是對數據庫二進制日志的備份

2 冷備

備份內容：數據表結構定義文件(*.frm)、共享表空間文件(ibdata)、獨立表空間文件(*.ibd)、重做日志文件(ib_logfile)，建議增加配置文件(my.cnf)

優勢：

• 備份簡單，僅需拷貝相關文件
• 備份文件有利于在不同操作系統、不同MySQL版本之間恢復
• 恢復簡單，僅需把文件恢復到指定位置
• 恢復速度快，無需執行SQL語句，無需重建索引

劣勢：

• 備份文件較大
• 跨平臺、操作系統、軟件版本可能會出現問題

3 邏輯備份

3.1 mysqldump

mysqldump -uroot -p [arguments] > file_name
#arguments參數
--all-databases #備份所有數據庫
--databases dbx #備份指定數據庫
--single-transaction dbx #備份數據庫的整體架構，事務控制保證備份的一致性
--lock-tables #一般用于MyISAM，備份時僅可讀
--lock-all-tables #對所有架構所有表上鎖
--add-drop-databases #在create前先運行drop，需配合--alldatabases使用
#通過轉存的文件建立一個salve replication
#value=1 轉存文件中記錄CHANGE MASTER語句
#value=2 CHANGE MASTER語句寫成注釋
--master-data[=value]?
--events #備份事件調度器
--routines #備份存儲過程和函數
--triggers #備份觸發器
--hex-blob #將binary、varbinary、blog、bit列類型備份為16進制的格式
--tab=path #產生tab分割的數據文件，可使用--fields-terminated-by改變分隔符
--where #導出給定條件的數據

3.2 SELECT … INTO OUTFILE

SELECT [column1],[column2] ...
INTO
#導出的文件名稱需擁有mysql用戶權限且文件不存在
OUTFILE 'filename'
{#fields or columns
#列分隔符
TERMINATED BY 'string'
#對于字符串的包含符
[OPTIONALLY] ENCLOSED BY 'char'
#轉譯符
ESCAPED BY 'char'
}
{#lines
STARTING BY 'string'
TERMINATED BY 'string'
}
FROM TABLE WHERE ...

3.3 邏輯備份的恢復

mysqldump備份的文件為邏輯SQL語句，可用以下兩種方法恢復

#在shell中執行
mysql -uroot -p < xxx.sql
#在MySQL交互命令行執行
mysql> source xxx.sql

3.4 LODA DATA INFILE

通過mysqldump --tab或select into outfile導出的數據需通過以下的命令導入

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'filename'
[REPLACE | IGNORE]
INTO TABLE tb_name
[CHARACTER SET charset_name]
#[FIELDS | COLUMNS]
TERMINATED BY 'string'
[OPTIONALLY] ENCLOSED BY 'char'
ESCAPED BY 'char'
#[LINES]
STARTING BY 'string'
TERMINATED BY 'string'
#[IGNORE NUMBER LINES]
col_name or user_var
SET col_name = expr

使用LOAD DATA INFILE導入需擁有FILE權限

mysql> load data infile 'xx.txt' into table a;

mysql> load data infile 'xx.txt' into table a;

4 binlog備份

在配置文件啟動binlog日志服務

#查看
mysqlbinlog mysqlbin-xxx.xxx
#還原
mysqlbinlog binlog.000001 | mysql -uroot -p test

因binlog為邏輯備份日志，可在MySQL命令行窗口使用source命令導入

也可設置偏移量指定二進制日志的位置，以跳過某些不想被恢復的語句

mysqlbinlog --start-position=10000 --stop-position=12000 | mysql -uroot =p test

也可使用–start-datetime和–stop-datetime

5 熱備

5.1 Ibackup

備份InnoDB表原理：

• 記錄備份開始的重做日志文件LSN
• 拷貝共享表空間文件和獨立表空間文件
• 拷貝后記錄重做日志的檢查點LSN
• 拷貝備份期間的重做日志

優點：

• 在線備份，不阻塞SQL語句
• 備份性能好，通過復制數據庫物理表空間文件和重做日志文件
• 支持壓縮備份
• 跨平臺支持

恢復流程：

• 恢復表空間文件
• 應用重做日志文件恢復InnoDB存儲引擎表

5.2 Xtrabackup

Xtrabackup是一個對InnoDB做數據備份的工具，支持在線熱備份（備份時不影響數據讀寫），是商業備份工具InnoDB Hotbackup的一個很好的替代品

5.2.1 全量備份

• 備份

• #備份
  /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --password=pass --host=127.1 --slave-info --tmpdir=/mysql/bak/full/ /mnt/sdfs/xxxx

  • 恢復

  • #1.停庫，清空data目錄
    mysqladmin -uroot -h127.1 -P3306 -p'123456' shutdown;
    rm -rf /mysql/data/*;

    #2.恢復-應用日志
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --apply-log --user=root --password=pass --host=127.1 /mnt/sdfs/***

    #3.恢復-拷貝文件
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root ?--password=pass --host=127.1 --copy-back /mnt/sdfs/***

    #4.文件修改權限
    chown -R mysql:mysql /mysql

    #5.啟動實例
    mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

    #6.[從庫]從庫需重新連接master
    change master to
    ? ? master_host='192.168.100.20',
    ? ? master_user='repl',
    ? ? master_port=3306,
    ? ? master_password='123456',
    ? ? master_log_file='mysql-bin.000001',
    ? ? master_log_pos=307;

    #7.[從庫]從庫啟動復制進程
    start slave; ? 啟動連接

    5.2.2 增量備份

    增量備份依賴于全備份，xtrabackup通過比較全備的終點與當前的LSN，生成區間內的備份文件，備份時也存在redo日志監控線程，備份過程中的增加的LSN也會寫入日志中

  • /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --user=root --host='127.0.0.1' --password='123456' --incremental ?/root/back --incremental-basedir=/opt/back/2022-05-13_17-46-53

    可進行多次增備，恢復需依次準備

  • #準備全備份日志
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log --redo-only ?/opt/back/2022-05-13_17-33-14
    #準備增備日志
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log --redo-only ?/opt/back/2022-05-13_17-33-14 --incremental-dir=/opt/back/2022-05-13_17-33-14
    #再次準備整體
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex --apply-log ?/opt/back/2022-05-13_17-33-14

    5.3 快照備份

    基于LVM對分區執行快照工作，Linux在硬盤和分區上建立一個邏輯層提高分區管理的靈活性，將離散的磁盤分區連接成一個整塊的卷組，形成存儲池

    LVM使用寫時復制技術創建快照，創建時僅拷貝元數據，不存在數據操作，創建過程較快。創建成功后，原始卷上有寫操作時快照會跟蹤原始卷的變化，將數據在改變之前拷貝到快照預留空間中，稱之寫時復制

    6 復制

    6.1 復制原理

    復制是MySQL提供的一種高可用、高性能的方案，用于建立大型應用，步驟分為三步：

    • 主服務器將數據更新到二進制日志中
    • 從服務器把主服務器的而二進制日志拷貝到自己的中繼日志中
    • 從服務器重做中繼日志，將更新應用到自己的數據庫中

    同步的過程是異步的，基本上也是實時進行的，取決于主服務器的壓力

    從服務器有兩個線程，

    • I/O線程：讀取主服務器的二進制日志，并保存為中繼日志
    • SQL線程：復制執行中繼日志

    6.2 快照+復制的備份架構

    復制用于備份的主要功能：

    • 數據分布
    • 讀取負載均衡
    • 數據庫備份
    • 高可用與故障轉移

    為解決數據誤操作的問題，可以在從服務器建立分區快照，避免復制對誤操作的處理能力